﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RoadTeam.Enums;
using System.Collections.Specialized;
namespace RoadTeam.DAL
{
    public class SqlHelper : HelpBase
    {

        private IDbConnection GetConn(String ConStr)
        {
            //String Str = GlobalConfig.ConnectionString;
            IConn = new SqlConnection(ConStr);
            if (IConn.State == ConnectionState.Closed)
            {
                IConn.Open();
            }
            return IConn;
        }

        public override int ExecuteNonQuery(string CmdText, CommandType CmdType, NameValueCollection NameValues, String ConStr)
        {
            ICmd = new SqlCommand();
            ICmd.Connection = GetConn(ConStr);
            ICmd.CommandText = CmdText;
            ICmd.CommandType = CmdType;
            GetParameter(ICmd, NameValues);
            return ICmd.ExecuteNonQuery();
        }


        public override DataTable ExecuteQuery(String CmdText, CommandType CmdType, NameValueCollection NameValues, String ConStr)
        {
            ICmd = new SqlCommand();
            ICmd.Connection = GetConn(ConStr);
            ICmd.CommandText = CmdText;
            ICmd.CommandType = CmdType;
            GetParameter(ICmd, NameValues);
            DataTable dt = new DataTable(); ;
            using (IDataReader sdr = ICmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        public override DataTable ExecuteQuery(String CmdText, CommandType CmdType, Dictionary<string, object> PreWhere, String ConStr)
        {
            ICmd = new SqlCommand();
            ICmd.Connection = GetConn(ConStr);
            ICmd.CommandText = CmdText;
            ICmd.CommandType = CmdType;
            GetParameter(ICmd, PreWhere);
            DataTable dt = new DataTable(); ;
            using (IDataReader sdr = ICmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        public override DataTable ExecuteStrQuery(String CmdText, CommandType CmdType, String ConStr)
        {
            ICmd = new SqlCommand();
            ICmd.Connection = GetConn(ConStr);
            ICmd.CommandText = CmdText;
            ICmd.CommandType = CmdType;
            DataTable dt = new DataTable(); ;
            using (IDataReader sdr = ICmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        public override int ExecuteNonQuery(string CmdText, CommandType CmdType, Dictionary<string, object> PreWhere, String ConStr)
        {
            ICmd = new SqlCommand();
            ICmd.Connection = GetConn(ConStr);
            ICmd.CommandText = CmdText;
            ICmd.CommandType = CmdType;
            GetParameter(ICmd, PreWhere);
            return ICmd.ExecuteNonQuery();
        }


        public override int InsertDataTable(String SelectCmd, DataTable dt, String TableName, List<string> IExcept, String ConStr)
        {
            String[] Query = new string[2];
            SqlDataAdapter Ada = new SqlDataAdapter();
            Ada.SelectCommand = new SqlCommand();
            Ada.SelectCommand.CommandText = SelectCmd;
            Ada.SelectCommand.Connection = (SqlConnection)GetConn(ConStr);
            SqlCommandBuilder Command = new SqlCommandBuilder(Ada);
            DataSet ds = new DataSet();
            Ada.Fill(ds, "Temp");

            Ada.InsertCommand = new SqlCommand("INSERT", (SqlConnection)GetConn(ConStr));
            SqlParameter para;
            foreach (DataColumn dc in dt.Columns)
            {
                if (IExcept.Count > 0 && IExcept.Contains(dc.ColumnName)) continue;
                para = CreateParameter(dc);
                Ada.InsertCommand.Parameters.Add(para);
                Query[0] += dc.ColumnName + ",";
                Query[1] += "@" + dc.ColumnName + ",";
            }
            Ada.InsertCommand.CommandText = @"INSERT INTO " + TableName + " (" + Query[0].TrimEnd(',') + ") Values (" + Query[1].TrimEnd(',') + ")";
            try
            {
                return Ada.Update(dt);
            }
            catch
            {
                return -1;
            }
            finally
            {
                IConn.Close();
            }

        }


        public override int UpdateDataTable(string StrSelectCmd, DataTable dt, String TableName, List<string> PrimaryKeyList, List<string> UpdateColumn, string ConStr)
        {
            SqlDataAdapter Ada = new SqlDataAdapter();
            Ada.SelectCommand = new SqlCommand();
            Ada.SelectCommand.CommandText = StrSelectCmd;
            Ada.SelectCommand.Connection = (SqlConnection)GetConn(ConStr);
            SqlCommandBuilder Command = new SqlCommandBuilder(Ada);
            DataSet ds = new DataSet();
            Ada.Fill(ds, "Temp");

            Ada.UpdateCommand = new SqlCommand("Update", (SqlConnection)GetConn(ConStr));
            String StrSet = String.Empty;
            String StrWhere = @" WHERE 1=1 ";
            SqlParameter para;
            foreach (DataColumn dc in dt.Columns)
            {
                para = CreateParameter(dc);
                Ada.UpdateCommand.Parameters.Add(para);

                if (PrimaryKeyList.Contains(dc.ColumnName))
                {
                    StrWhere += " AND " + dc.ColumnName + "=@" + dc.ColumnName + ",";
                }

                if (UpdateColumn.Contains(dc.ColumnName))
                {
                    StrSet += dc.ColumnName + "=@" + dc.ColumnName + ",";
                }
            }
            Ada.UpdateCommand.CommandText = @"UPDATE " + TableName + " SET  " + StrSet.TrimEnd(',') + StrWhere.TrimEnd(',');
            try
            {
                return Ada.Update(dt);
            }
            catch
            {
                return -1;
            }
            finally
            {
                IConn.Close();
            }
        }






        private SqlParameter CreateParameter(DataColumn dc)
        {
            SqlParameter para = new SqlParameter();
            para.SourceColumn = dc.ColumnName;
            para.ParameterName = "@" + dc.ColumnName;
            para.DbType = GetType(dc.DataType);
            return para;
        }
        private void GetParameter(IDbCommand ICmd, Dictionary<string, object> PreWhere)
        {
            IDbDataParameter para;
            List<String> likey = new List<string>(PreWhere.Keys);
            for (int i = 0; i < likey.Count; i++)
            {
                para = ICmd.CreateParameter();
                para.ParameterName = likey[i];
                para.Value = PreWhere[likey[i]];
                ICmd.Parameters.Add(para);
            }
        }
        private void GetParameter(IDbCommand ICmd, NameValueCollection NameValues)
        {
            IDbDataParameter para;
            for (int i = 0; i < NameValues.Count; i++)
            {
                para = ICmd.CreateParameter();
                para.ParameterName = NameValues.GetKey(i);
                para.Value = NameValues.GetValues(NameValues.GetKey(i))[0].ToString();
                ICmd.Parameters.Add(para);
            }
        }
        public DbType GetType(Type type)
        {
            DbType result = DbType.String;
            if (type.Equals(typeof(int)) || type.IsEnum)
                result = DbType.Int32;
            else if (type.Equals(typeof(long)))
                result = DbType.Int64;
            else if (type.Equals(typeof(double)) || type.Equals(typeof(Double)))
                result = DbType.Decimal;
            else if (type.Equals(typeof(DateTime)))
                result = DbType.DateTime;
            else if (type.Equals(typeof(bool)))
                result = DbType.Boolean;
            else if (type.Equals(typeof(string)))
                result = DbType.String;
            else if (type.Equals(typeof(decimal)))
                result = DbType.Decimal;
            else if (type.Equals(typeof(byte[])))
                result = DbType.Binary;
            else if (type.Equals(typeof(byte)))
                result = DbType.Byte;
            else if (type.Equals(typeof(Guid)))
                result = DbType.Guid;
            return result;
        }
    }
}
